local data_out_dir ./data/cleaned
local data_in_dir ./data/raw

import excel `data_in_dir'/st_fips_codes.xls, firstrow case(lower) clear
collapse (first) state, by(fipsstate)
destring fipsstate, generate(st_fips)
save `data_in_dir'/stfips, replace

import delimited `data_in_dir'/packagechng_imp.csv, case(lower) varnames(1) clear
replace state = "District of Columbia" if state == "D.C."
merge m:1 state using `data_in_dir'/stfips
/*
    Result                           # of obs.
    -----------------------------------------
    not matched                             0
    matched                               357  (_merge==3)
    -----------------------------------------
*/
drop _merge

sort state year
* generate a full set of years from 1997-2017 (square up the dataset)
expand 10 if year == 2006, generate(yeardec)
replace yeardec = yeardec*(-1)
gsort state year -yeardec
bys state: replace yeardec = sum(yeardec) if year <= 2006
replace year = year + yeardec
drop yeardec
expand 6 if year == 2012, generate(yearinc)
gsort state year yearinc
bys state: replace yearinc = sum(yearinc)
replace year = year + yearinc
drop yearinc
*make months
expand 12, generate(month)
sort state year month
bys state year: replace month = sum(month)
bys state year: replace month = month + 1
* convert imp fraction into implementation month
generate imp_mo = 12*(1-imp) if inrange(imp,0.05,1)
rename imp imp_yr
generate imp_month = (month > imp_mo) if !missing(imp_mo)
replace imp_month = 0 if missing(imp_mo)
drop imp_mo
order state year month imp_yr imp_month
save `data_out_dir'/packagechng_imp, replace
